package org.jeecgframework.web.service.impl;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.jeecgframework.core.common.service.impl.CommonServiceImpl;
import org.jeecgframework.core.util.DateUtils;
import org.jeecgframework.core.util.ResourceUtil;
import org.jeecgframework.core.util.StringUtil;
import org.jeecgframework.web.service.ZyIpReportXXServiceI;
import org.jeecgframework.web.system.pojo.base.TSDepart;
import org.jeecgframework.web.system.pojo.base.TSType;
import org.jeecgframework.web.system.pojo.base.TSUser;
import org.jeecgframework.web.system.service.UserService;
import org.jeecgframework.web.util.StaticValue;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service("zyIpReportXXService")
@Transactional
public class ZyIpReportXXServiceImpl extends CommonServiceImpl implements ZyIpReportXXServiceI {
	@Autowired
	private UserService userService;

	public String[] getChainBpCode(String bpCode){
		List<TSType> types = userService.findHql("from TSType where TSTypegroup.typegroupcode='"+StaticValue.DIC_CHAIN_REPORT+" 'and typecode like '%"+bpCode+"%'");
		if(types.size() > 0){
			TSType tp = types.get(0);
				if(!"".equals(tp.getTypecode())){
	                return tp.getTypecode().split(",");
	            }
	        }
        //todo 暂时修改-小红优化
		return new String[]{"nobpCode"};
	}
	

	private static Integer planZY = 1835;//中影
	private static Integer planJY = 519;//金逸
	private static Integer planDD = 105;//大地
	private static Integer planCGV = 313;//CGV
	private static Integer planHD = 315;//横店
	
	@Override
	public Map<String, Object> queryIpReportBPieChartData(String irExType, String chain, String typeId, Integer year) {
		Map<String, Object> map = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		Integer curYear = DateUtils.getYear();
		String midNight = "23:59:59", beginDate = DateUtils.getYear() + "-01-01 " + midNight;//当年1月1日

		sql.append(" SELECT  ");
		sql.append("     COUNT(ttt.total) AS total, ");
		if(StringUtil.isNotEmpty(irExType)){
			switch(irExType){
				case "1":
					sql.append(" IFNULL( SUM(IF(ttt.room_temp > 0,1,0)), 0) AS exCount, ");
					sql.append(" IFNULL( SUM(ttt.room_temp), 0) AS room_temp ");
					break;
				case "2":
					sql.append(" IFNULL( SUM(IF(ttt.room_hum > 0,1,0)), 0) AS exCount, ");
					sql.append(" IFNULL( SUM(ttt.room_hum), 0) AS room_hum ");
					break;
				case "3":
					sql.append(" IFNULL( SUM(IF(ttt.v_3 > 0,1,0)), 0) AS exCount, ");
					sql.append(" IFNULL( SUM(ttt.v_3), 0) AS v_3 ");
					break;
				case "4":
					sql.append(" IFNULL( SUM(IF(ttt.v_1 > 0,1,0)), 0) AS exCount, ");
					sql.append(" IFNULL( SUM(ttt.v_1), 0) AS v_1 ");
					break;
				case "5":
					sql.append(" IFNULL( SUM(IF(ttt.v_0 > 0,1,0)), 0) AS exCount, ");
					sql.append(" IFNULL( SUM(ttt.v_0), 0) AS v_0 ");
					break;
				case "6":
					sql.append(" IFNULL( SUM(IF(ttt.fl_before > 0,1,0)), 0) AS exCount, ");
					sql.append(" IFNULL( SUM(ttt.fl_before), 0) AS fl_before ");
					break;
			}
		}else{
			sql.append("     IFNULL( SUM(IF(ttt.room_temp + ttt.room_hum + ttt.v_3 + ttt.v_1 + ttt.v_0 + ttt.fl_before > 0,1,0)), 0) AS exCount, ");
			sql.append("     IFNULL( SUM(room_temp), 0) AS room_temp, IFNULL( SUM(room_hum), 0) AS room_hum, IFNULL( SUM(v_3), 0) AS v_3, IFNULL( SUM(v_1), 0) AS v_1, IFNULL( SUM(v_0), 0) AS v_0, IFNULL( SUM(fl_before), 0) AS fl_before ");
		}
		sql.append(" FROM( ");
		
		if(curYear.equals(year)){//真实表
			Date spDate = DateUtils.str2Date(curYear + "-01-08 " + midNight, DateUtils.datetimeFormat);
			if(new Date().getTime() > spDate.getTime()){
				
				Calendar cal = Calendar.getInstance();
				cal.add(Calendar.DAY_OF_YEAR, -8);
				String reduce15Date = DateUtils.date2Str(cal.getTime(), DateUtils.date_sdf) + " " + midNight;//当前日期 往前 8天日期
				sql.append(" SELECT ")
					.append(" 	COUNT(ir.inspection_plan_id) AS total, ")
					.append(" 	IFNULL(IF(ir.yx_light_center < 11 , 1, 0) ,0 ) AS fl_before, ")
					.append(" 	IFNULL(IF(ir.yt_rm_temperature > 35 ,1 ,0) ,0 ) AS room_temp , ")
					.append(" 	IFNULL(IF(ir.yt_rm_humidity > 80 ,1 ,0) ,0 ) AS room_hum, ")
					.append(" 	CASE WHEN ir.xl_voltage_type = 1 ")
					.append(" 		THEN  ")
					.append(" 			IFNULL(CASE WHEN FIND_IN_SET(et.type_desc, 'DP2K-32B,DP4K-32B') > 0  ")
					.append(" 								THEN  ")
					.append(" 									CASE WHEN ir.xl_voltage > 418 THEN 1 WHEN ir.xl_voltage != 0 && ir.xl_voltage < 342 THEN 1 ELSE 0 END  ")
					.append(" 								ELSE 0  ")
					.append(" 						END,0) ")
					.append("		ELSE 0 ")
					.append(" 	END AS v_3, ")
					.append(" 	CASE WHEN ir.xl_voltage_type = 2 ")
					.append(" 		THEN  ")
					.append(" 			IFNULL(CASE WHEN FIND_IN_SET(et.type_desc, 'DP2K-32B,DP4K-32B') = 0  ")
					.append(" 								THEN  ")
					.append(" 									CASE WHEN ir.xl_voltage > 242 THEN 1 WHEN ir.xl_voltage != 0 && ir.xl_voltage < 198 THEN 1 ELSE 0 END ")
					.append(" 								ELSE 0  ")
					.append(" 						END,0) ")
					.append("		ELSE 0 ")
					.append(" 	END AS v_1, ")
					.append(" 	IFNULL( IF(ir.xl_zero_voltage > 2 ,1 ,0) ,0 )AS v_0  ")
					.append(" FROM ")
					.append(" 	zy_inspection_report ir ")
					.append(" LEFT JOIN zy_inspection_plan ip on ip.id = ir.inspection_plan_id ")
					.append(" LEFT JOIN zy_equipment_spare es on es.id = ip.equipment_spare_id ")
					.append(" LEFT JOIN zy_equipment_type et on et.id = es.type ")
					.append(" LEFT JOIN ( SELECT DISTINCT bp_code FROM t_s_depart ) dp ON dp.bp_code = es.bp_no ")
					.append(" LEFT JOIN (SELECT * FROM zy_plan_record WHERE plan_state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("') pr ON pr.bus_id = ip.id ")
					.append(" WHERE ")
					.append("	pr.create_date BETWEEN '").append(beginDate).append("' AND '").append(reduce15Date).append("' ")
					.append(" AND ip.state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' ");
			}else{
				map.put("total", 0);
				map.put("exCount", 0);	
				map.put("exCountList", new Integer[]{0,0,0,0,0,0});
				return map;
			}
		}else if ("2016".equals(year)) {//历史表
			sql.append("     SELECT ");
			sql.append("         COUNT(rt.sn) AS total, ");
			sql.append("         IFNULL(IF(rt.room_temp > 35, 1, 0),0) AS room_temp, ");
			sql.append("         IFNULL(IF(rt.room_hum > 80, 1, 0),0) AS room_hum, ");
			sql.append("         IFNULL(CASE WHEN FIND_IN_SET(et.type_desc, 'DP2K-32B,DP4K-32B') > 0 THEN CASE WHEN rt.v_3 > 418 THEN 1 WHEN CAST(rt.v_3 AS SIGNED INTEGER) != 0 && rt.v_3 < 342 THEN 1 ELSE 0 END ELSE 0 END,0) AS v_3, ");
			sql.append("         IFNULL(CASE WHEN FIND_IN_SET(et.type_desc, 'DP2K-32B,DP4K-32B') = 0 THEN CASE WHEN rt.v_1 > 242 THEN 1 WHEN CAST(rt.v_1 AS SIGNED INTEGER) != 0 && rt.v_1 < 198 THEN 1 ELSE 0 END ELSE 0 END,0) AS v_1, ");
			sql.append("         IFNULL(IF(rt.v_0 > 2, 1, 0), 0) AS v_0, ");
			sql.append("         IFNULL(IF(CAST(rt.fl_before AS SIGNED INTEGER) != 0 && rt.fl_before < 11, 1, 0),0) AS fl_before ");
			sql.append("     FROM ");
			sql.append("         cbs_report_temp rt ");
			sql.append("     LEFT JOIN zy_equipment_spare es ON es.sn = rt.sn ");
			sql.append("     LEFT JOIN zy_equipment_type et ON et.id = es.type ");
			sql.append("     LEFT JOIN ( ");
			sql.append("         SELECT DISTINCT ");
			sql.append("             bp_code ");
			sql.append("         FROM ");
			sql.append("             t_s_depart ");
			sql.append("     ) dp ON dp.bp_code = es.bp_no ");
			sql.append("     WHERE ");
			sql.append("         1 = 1 ");
		} else {
			beginDate = year + "-01-01 " + midNight;
			String reduce15Date = year + "-12-31 " + midNight;//最后一天
			sql.append(" SELECT ")
				.append(" 	COUNT(ir.inspection_plan_id) AS total, ")
				.append(" 	IFNULL(IF(ir.yx_light_center < 11 , 1, 0) ,0 ) AS fl_before, ")
				.append(" 	IFNULL(IF(ir.yt_rm_temperature > 35 ,1 ,0) ,0 ) AS room_temp , ")
				.append(" 	IFNULL(IF(ir.yt_rm_humidity > 80 ,1 ,0) ,0 ) AS room_hum, ")
				.append(" 	CASE WHEN ir.xl_voltage_type = 1 ")
				.append(" 		THEN  ")
				.append(" 			IFNULL(CASE WHEN FIND_IN_SET(et.type_desc, 'DP2K-32B,DP4K-32B') > 0  ")
				.append(" 								THEN  ")
				.append(" 									CASE WHEN ir.xl_voltage > 418 THEN 1 WHEN ir.xl_voltage != 0 && ir.xl_voltage < 342 THEN 1 ELSE 0 END  ")
				.append(" 								ELSE 0  ")
				.append(" 						END,0) ")
				.append("		ELSE 0 ")
				.append(" 	END AS v_3, ")
				.append(" 	CASE WHEN ir.xl_voltage_type = 2 ")
				.append(" 		THEN  ")
				.append(" 			IFNULL(CASE WHEN FIND_IN_SET(et.type_desc, 'DP2K-32B,DP4K-32B') = 0  ")
				.append(" 								THEN  ")
				.append(" 									CASE WHEN ir.xl_voltage > 242 THEN 1 WHEN ir.xl_voltage != 0 && ir.xl_voltage < 198 THEN 1 ELSE 0 END ")
				.append(" 								ELSE 0  ")
				.append(" 						END,0) ")
				.append("		ELSE 0 ")
				.append(" 	END AS v_1, ")
				.append(" 	IFNULL( IF(ir.xl_zero_voltage > 2 ,1 ,0) ,0 )AS v_0  ")
				.append(" FROM ")
				.append(" 	zy_inspection_report ir ")
				.append(" LEFT JOIN zy_inspection_plan ip on ip.id = ir.inspection_plan_id ")
				.append(" LEFT JOIN zy_equipment_spare es on es.id = ip.equipment_spare_id ")
				.append(" LEFT JOIN zy_equipment_type et on et.id = es.type ")
				.append(" LEFT JOIN ( SELECT DISTINCT bp_code FROM t_s_depart ) dp ON dp.bp_code = es.bp_no ")
				.append(" LEFT JOIN (SELECT * FROM zy_plan_record WHERE plan_state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("') pr ON pr.bus_id = ip.id ")
				.append(" WHERE ")
				.append("	pr.create_date BETWEEN '").append(beginDate).append("' AND '").append(reduce15Date).append("' ")
				.append(" AND ip.state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' ");
		}
		
		TSUser user = ResourceUtil.getSessionUserName();
		TSDepart depart = user.getCurrentDepart();
		if(userService.hasRole(user.getUserName(),StaticValue.CHAIN_ROLE_LEADER)){//院线用户
			String[] dpcodes = getChainBpCode(depart.getBpCode());
			sql.append(" AND dp.bp_code IN (");
			for(int i = 0;i < dpcodes.length;i++){
				if(i != dpcodes.length - 1){
					sql.append("'"+ dpcodes[i] +"',");
				}else{
					sql.append("'"+ dpcodes[i] +"'");
				}
			}
			sql.append(" )");
		}else if(!chain.equals("")){
			sql.append(" AND es.cinema IN( ");
			String[] dpCodes = chain.split(",");
			for (int i = 0; i < dpCodes.length; i++) {
				if(i != dpCodes.length - 1){
					sql.append("'"+ dpCodes[i] +"',");
				}else{
					sql.append("'"+ dpCodes[i] +"'");
				}
			}
			sql.append(") ");
		}
		
		if(!typeId.equals("")){
			sql.append(" AND es.type='").append(typeId).append("' ");
		}
		if(!"2016".equals(year)){//真实表
			sql.append("     GROUP BY ");
			sql.append("         ir.inspection_plan_id ");
		}else{
			sql.append("     GROUP BY ");
			sql.append("         rt.sn ");
		}
		sql.append(" ) AS ttt ");
		
		
		Map<String,Object> exRsMap = super.findOneForJdbc(sql.toString());

		List<Integer> exCountList = new ArrayList<Integer>();
		if(null != exRsMap && exRsMap.size() > 0){
			if(StringUtil.isNotEmpty(exRsMap.get("fl_before"))){
				exCountList.add(Integer.valueOf(exRsMap.get("fl_before").toString()));
			}
			if(StringUtil.isNotEmpty(exRsMap.get("room_temp"))){
				exCountList.add(Integer.valueOf(exRsMap.get("room_temp").toString()));
			}
			if(StringUtil.isNotEmpty(exRsMap.get("room_hum"))){
				exCountList.add(Integer.valueOf(exRsMap.get("room_hum").toString()));
			}
			if(StringUtil.isNotEmpty(exRsMap.get("v_3"))){
				exCountList.add(Integer.valueOf(exRsMap.get("v_3").toString()));
			}
			if(StringUtil.isNotEmpty(exRsMap.get("v_1"))){
				exCountList.add(Integer.valueOf(exRsMap.get("v_1").toString()));
			}
			if(StringUtil.isNotEmpty(exRsMap.get("v_0"))){
				exCountList.add(Integer.valueOf(exRsMap.get("v_0").toString()));
			}
		}
		
		if(null != exRsMap && exRsMap.size() > 0){
			if(StringUtil.isNotEmpty(exRsMap.get("total")) && StringUtil.isNotEmpty(exRsMap.get("exCount"))){
				map.put("total", Integer.valueOf(exRsMap.get("total").toString()) - Integer.valueOf(exRsMap.get("exCount").toString()));
				map.put("exCount", Integer.valueOf(exRsMap.get("exCount").toString()));
				map.put("exCountList", exCountList);
			}else{
				map.put("total", 0);
				map.put("exCount", 0);	
				map.put("exCountList", null);
			}
		}
		return map;
	}

	private Integer planCountPlus(String dpCode){
		switch(dpCode){
			case "350001619":
				return planZY;
			case "350000918":
				return planZY;
			case "350000971":
				return planJY;
			case "350000980":
				return planDD;
			case "350001738":
				return planDD;
			case "350001055":
				return planCGV;
			case "350000959":
				return planHD;
		}
		return 0;
	}

	@Override
	public Map<String, Object> query2016IpProgress(String chain, String typeId,String territoryL2, String territoryL3) {
		Map<String, Object> rsMap = new HashMap<String, Object>();
		
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT ");
		sql.append("   COUNT(es.sn) AS esCount ");
		sql.append(" FROM ");
		sql.append("   cbs_report_temp rt ");
		sql.append(" LEFT JOIN zy_equipment_spare es ON rt.sn = es.sn ");
		sql.append(" LEFT JOIN (SELECT DISTINCT bp_code FROM t_s_depart) dp ON dp.bp_code = es.bp_no ");
		sql.append(" LEFT JOIN t_s_territory tt ON tt.id = es.city_id ");
		sql.append(" WHERE ");
		sql.append("   es.is_spare = 0 ");
		TSUser user = ResourceUtil.getSessionUserName();
		TSDepart depart = user.getCurrentDepart();
		Integer planCount = 0;
		if(userService.hasRole(user.getUserName(),StaticValue.CHAIN_ROLE_LEADER)){//院线用户
			String[] dpcodes = this.getChainBpCode(depart.getBpCode());
			sql.append(" AND dp.bp_code IN (");
			for(int i = 0;i < dpcodes.length;i++){
				if(i != dpcodes.length - 1){
					sql.append("'"+ dpcodes[i] +"',");
				}else{
					sql.append("'"+ dpcodes[i] +"'");
				}
				planCount += planCountPlus(depart.getBpCode());
			}
			sql.append(" )");
		}else{
			if(StringUtil.isNotEmpty(chain)){
				planCount = 0;
				sql.append(" AND es.cinema IN( ");
				String[] dpCodes = chain.split(",");
				for (int i = 0; i < dpCodes.length; i++) {
					if(i != dpCodes.length - 1){
						sql.append("'"+ dpCodes[i] +"',");
					}else{
						sql.append("'"+ dpCodes[i] +"'");
					}
					planCount += planCountPlus(dpCodes[i]);
				}
				sql.append(") ");
			}else{
				planCount += 8448;
			}
		}
		if(StringUtil.isNotEmpty(typeId)){
			sql.append(" AND es.type = '"+ typeId +"' ");
		}
		if(StringUtil.isNotEmpty(territoryL2)){
			sql.append(" AND tt.territoryparentid = '"+ territoryL2 +"' ");
		}
		if(StringUtil.isNotEmpty(territoryL3)){
			sql.append(" AND tt.id = '"+ territoryL3 +"' ");
		}
		Map<String, Object> map = super.findOneForJdbc(sql.toString());
		
		rsMap.put("planCount", planCount);//历史计划量
		rsMap.put("actuCount", map.get("esCount"));//历史实际量
		
		return rsMap;
	}


	@Override
	public Map<String, Object> query2017IpProgress(String chain, String typeId,String territoryL2, String territoryL3,Integer year) {
		TSUser user = ResourceUtil.getSessionUserName();
		TSDepart depart = user.getCurrentDepart();
		Map<String, Object> rsMap = new HashMap<>();
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT ");
		sql.append(" 	YEAR(ip.ip_plan_date) AS ipYear, MONTH(ip.ip_plan_date) AS ipMonth, ");
//		sql.append("	COUNT(ip.id) AS countByMonth, ");
		sql.append("	SUM(CASE ip.state WHEN '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL).append("' THEN 0 ELSE 1 END) AS countByMonth, ");//取消的巡检计划不算在统计范围内
		sql.append("	SUM(CASE ip.state WHEN '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' THEN 1 ELSE 0 END) AS completeCount ");
		sql.append(" FROM ");
		sql.append(" 	zy_equipment_spare es ");
		sql.append(" LEFT JOIN zy_inspection_plan ip ON ip.equipment_spare_id = es.id ");
		sql.append(" LEFT JOIN (SELECT DISTINCT bp_code FROM t_s_depart) dp ON dp.bp_code = es.bp_no ");
		sql.append(" LEFT JOIN t_s_territory tt ON tt.id = es.city_id ");
		sql.append(" WHERE ");
		sql.append(" 	YEAR(ip.ip_plan_date) = ");
		sql.append(year);
		sql.append(" ");
		
		StringBuffer sqlb = new StringBuffer();
		sqlb.append(" SELECT ");
		sqlb.append(" 	YEAR(pr.create_date) AS ipYear, MONTH(pr.create_date) AS ipMonth, ");
		sqlb.append("	SUM(CASE ip.state WHEN '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' THEN 1 ELSE 0 END) AS completeCount ");
		sqlb.append(" FROM ");
		sqlb.append(" 	zy_equipment_spare es ");
		sqlb.append(" LEFT JOIN zy_inspection_plan ip ON ip.equipment_spare_id = es.id ");
		sqlb.append(" LEFT JOIN (SELECT * FROM zy_plan_record WHERE plan_state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' GROUP BY bus_id) pr on pr.bus_id = ip.id ");
		sqlb.append(" LEFT JOIN (SELECT DISTINCT bp_code FROM t_s_depart) dp ON dp.bp_code = es.bp_no ");
		sqlb.append(" LEFT JOIN t_s_territory tt ON tt.id = es.city_id ");
		sqlb.append(" WHERE ");
		sqlb.append(" 	YEAR(pr.create_date) = ");
		sqlb.append(year);
		sqlb.append(" ");
		
		
		if(userService.hasRole(user.getUserName(),StaticValue.CHAIN_ROLE_LEADER)){//院线用户
			String[] dpcodes = this.getChainBpCode(depart.getBpCode());
			sql.append(" AND dp.bp_code IN (");
			sqlb.append(" AND dp.bp_code IN (");
			for(int i = 0;i < dpcodes.length;i++){
				if(i != dpcodes.length - 1){
					sql.append("'"+ dpcodes[i] +"',");
					sqlb.append("'"+ dpcodes[i] +"',");
				}else{
					sql.append("'"+ dpcodes[i] +"'");
					sqlb.append("'"+ dpcodes[i] +"'");
				}
			}
			sql.append(" )");
			sqlb.append(" )");
		}
		if(StringUtil.isNotEmpty(chain)){
			sqlb.append(" AND es.cinema IN( ");
			sql.append(" AND es.cinema IN( ");
			String[] dpCodes = chain.split(",");
			for (int i = 0; i < dpCodes.length; i++) {
				if(i != dpCodes.length - 1){
					sqlb.append("'"+ dpCodes[i] +"',");
					sql.append("'"+ dpCodes[i] +"',");
				}else{
					sqlb.append("'"+ dpCodes[i] +"'");
					sql.append("'"+ dpCodes[i] +"'");
				}
			}
			sqlb.append(") ");
			sql.append(") ");
		}
		if(StringUtil.isNotEmpty(typeId)){
			sqlb.append(" AND es.type = '"+ typeId +"' ");
			sql.append(" AND es.type = '"+ typeId +"' ");
		}
		if(StringUtil.isNotEmpty(territoryL2)){
			sqlb.append(" AND tt.territoryparentid = '"+ territoryL2 +"' ");
			sql.append(" AND tt.territoryparentid = '"+ territoryL2 +"' ");
		}
		if(StringUtil.isNotEmpty(territoryL3)){
			sqlb.append(" AND tt.id = '"+ territoryL3 +"' ");
			sql.append(" AND tt.id = '"+ territoryL3 +"' ");
		}
		
		sql.append(" GROUP BY ");
		sql.append(" 	ipYear, ipMonth ");
		List<Map<String, Object>> rsMapList = super.findForJdbc(sql.toString());
		List<Integer> planEachMonth = new ArrayList<Integer>();
		List<Integer> completeEachMonth = new ArrayList<Integer>();
		for(int i = 1;i <= 12;i++){
			if(null != rsMapList && rsMapList.size() > 0){
				boolean hadRecord = false;
				for (Map<String, Object> mp : rsMapList) {
					if(StringUtil.isNotEmpty(mp.get("ipMonth")) && Integer.valueOf(mp.get("ipMonth").toString()) == i){
						if(StringUtil.isNotEmpty(mp.get("countByMonth"))){
							planEachMonth.add(Integer.valueOf(mp.get("countByMonth").toString()));
							hadRecord = true;
						}
//						if(StringUtil.isNotEmpty(mp.get("completeCount"))){
//							completeEachMonth.add(Integer.valueOf(mp.get("completeCount").toString()));
//							hadRecord2 = true;
//						}
					}
				}
				if(!hadRecord){
					planEachMonth.add(0);
				}
//				if(!hadRecord2){
//					completeEachMonth.add(0);
//				}
			}else{
				planEachMonth.add(0);
				//completeEachMonth.add(0);
			}
		}
		
		sqlb.append(" GROUP BY ");
		sqlb.append(" 	ipYear, ipMonth ");
		List<Map<String, Object>> rsMapbList = super.findForJdbc(sqlb.toString());
		for(int i = 1;i <= 12;i++){
			if(null != rsMapbList && rsMapbList.size() > 0){
				boolean hadRecord = false;
				for (Map<String, Object> mp : rsMapbList) {
					if(StringUtil.isNotEmpty(mp.get("ipMonth")) && Integer.valueOf(mp.get("ipMonth").toString()) == i){
						if(StringUtil.isNotEmpty(mp.get("completeCount"))){
							completeEachMonth.add(Integer.valueOf(mp.get("completeCount").toString()));
							hadRecord = true;
						}
					}
				}
				if(!hadRecord){
					completeEachMonth.add(0);
				}
			}else{
				completeEachMonth.add(0);
			}
		}
		rsMap.put("planEachMonth", planEachMonth);//2017 计划
		rsMap.put("completeEachMonth", completeEachMonth);//2017 完成
		
		return rsMap;
	}

	
	@Override
	public JSONObject queryLinePieChartData(String chain, String typeId, String territoryL2, String territoryL3, Integer year) {
		TSUser user = ResourceUtil.getSessionUserName();
		TSDepart depart = user.getCurrentDepart();
		StringBuilder conditionSql = new StringBuilder();
		if(userService.hasRole(user.getUserName(),StaticValue.CHAIN_ROLE_LEADER)){//院线用户
			String[] dpcodes = this.getChainBpCode(depart.getBpCode());
			conditionSql.append(" AND dp.bp_code IN (");
			for(int i = 0;i < dpcodes.length;i++){
				if(i != dpcodes.length - 1){
					conditionSql.append("'"+ dpcodes[i] +"',");
				}else{
					conditionSql.append("'"+ dpcodes[i] +"'");
				}
			}
			conditionSql.append(" )");
		}
		if(StringUtil.isNotEmpty(chain)){
			conditionSql.append(" AND es.cinema IN( ");
			String[] dpCodes = chain.split(",");
			for (int i = 0; i < dpCodes.length; i++) {
				if(i != dpCodes.length - 1){
					conditionSql.append("'"+ dpCodes[i] +"',");
				}else{
					conditionSql.append("'"+ dpCodes[i] +"'");
				}
			}
			conditionSql.append(") ");
		}
		if(StringUtil.isNotEmpty(typeId)){
			conditionSql.append(" AND es.type = '"+ typeId +"' ");
		}
		if(StringUtil.isNotEmpty(territoryL2)){
			conditionSql.append(" AND tt.territoryparentid = '"+ territoryL2 +"' ");
		}
		if(StringUtil.isNotEmpty(territoryL3)){
			conditionSql.append(" AND tt.id = '"+ territoryL3 +"' ");
		}
		
		JSONObject json = new JSONObject();
		StringBuilder sql = new StringBuilder();
		//计划巡检数量
		sql.append(" SELECT ");
		sql.append(" 	COUNT(*) AS countByMonth, ");
		sql.append(" 	MONTH (ip.ip_plan_date) AS ipMonth ");
		sql.append(" FROM ");
		sql.append(" 	zy_inspection_plan ip ");
		sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
		sql.append(" LEFT JOIN (SELECT DISTINCT bp_code FROM t_s_depart) dp ON dp.bp_code = es.bp_no ");
		sql.append(" LEFT JOIN t_s_territory tt ON tt.id = es.city_id ");
		sql.append(" WHERE ");
		sql.append("	YEAR(ip.ip_plan_date) = '").append(year).append("' ");
		sql.append("	AND ip.state != '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_CANCEL).append("' ");//不包括 取消的计划
		sql.append(conditionSql);//拼接条件
		sql.append(" GROUP BY ");
		sql.append(" 	ipMonth; ");
		List<Map<String, Object>> mapList = super.findForJdbc(sql.toString());
		List<Integer> planEachMonth = new ArrayList<Integer>();
		int countPlan = 0;
		for(int i = 1;i < 13;i++){
			if(null != mapList && mapList.size() > 0){
				boolean hadRecord = false;
				for (Map<String, Object> mp : mapList) {
					if(StringUtil.isNotEmpty(mp.get("ipMonth")) && Integer.valueOf(mp.get("ipMonth").toString()) == i){
						if(StringUtil.isNotEmpty(mp.get("countByMonth"))){
							countPlan += Integer.valueOf(mp.get("countByMonth").toString());
							planEachMonth.add(countPlan);
							hadRecord = true;
						}
					}
				}
				if(!hadRecord){
					planEachMonth.add(countPlan);
				}
			}else{
				planEachMonth.add(0);
			}
		}
		
		json.accumulate("planCountEachMonth", planEachMonth);
		
		sql = new StringBuilder();
		//实际完成巡检数量
		sql.append(" SELECT ");
		sql.append(" 	COUNT(*) AS countByMonth, ");
		sql.append(" 	MONTH (pr.create_date) AS ipMonth ");
		sql.append(" FROM zy_inspection_plan ip ");
		sql.append(" LEFT JOIN zy_equipment_spare es ON es.id = ip.equipment_spare_id ");
		sql.append(" LEFT JOIN (SELECT DISTINCT bp_code FROM t_s_depart) dp ON dp.bp_code = es.bp_no ");
		sql.append(" LEFT JOIN t_s_territory tt ON tt.id = es.city_id ");
		sql.append(" LEFT JOIN (SELECT * FROM zy_plan_record WHERE plan_state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("') pr ON pr.bus_id = ip.id ");
		sql.append(" WHERE ");
		sql.append("	YEAR(pr.create_date) = '").append(year).append("' ");
		sql.append("	AND ip.state = '").append(StaticValue.DIC_PLAN_STATE_INSPECTION_COMPLETE).append("' ");
		sql.append(conditionSql);//拼接条件
		sql.append(" GROUP BY ");
		sql.append(" 	ipMonth; ");
		mapList = super.findForJdbc(sql.toString());
		List<Integer> actEachMonth = new ArrayList<Integer>();
		int countDone = 0;
		for(int i = 1;i < 13;i++){
			if(null != mapList && mapList.size() > 0){
				boolean hadRecord = false;
				for (Map<String, Object> mp : mapList) {
					if(StringUtil.isNotEmpty(mp.get("ipMonth")) && Integer.valueOf(mp.get("ipMonth").toString()) == i){
						if(StringUtil.isNotEmpty(mp.get("countByMonth"))){
							countDone += Integer.valueOf(mp.get("countByMonth").toString());
							actEachMonth.add(countDone);
							hadRecord = true;
						}
					}
				}
				if(!hadRecord){
					actEachMonth.add(countDone);
				}
			}else{
				actEachMonth.add(0);
			}
		}
		json.accumulate("actEachMonth", actEachMonth);
		
		json.accumulate("countDone", countDone);//已完成
		json.accumulate("countPlan", countPlan - countDone);//未完成
		
		return json;
	}





 	
 	
 	
}